package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.bean.Express;
import com.kaikeba.dao.BaseCourierDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: ManolinCoder
 * @time: 2021/9/3
 */
public class CourierDaoMysql implements BaseCourierDao {

    //用于查询数据库中的全部快递员信息（总数+新增）
    public static final String C_CONSOLE = "SELECT " +
            "COUNT(ID) data_size," +
            "COUNT(TO_DAYS(CINTIME)=TO_DAYS(NOW()) OR NULL) data_day" +
            " FROM ECOURIER";
    //用于查询所有快递员信息(未分页)
    public static final String C_FIND_ALL = "SELECT * FROM ECOURIER";
    //用于查询所有快递员信息(分页)
    public static final String C_FIND_LIMIT = "SELECT * FROM ECOURIER LIMIT ?,?";
    //根据快递员电话号码，查询快递员信息
    public static final String C_FIND_BY_PHONENUMBER = "SELECT * FROM ECOURIER WHERE CPHONENUMBER=?";
    //根据快递员id，查询快递员信息
    public static final String C_FIND_BY_ID = "SELECT * FROM ECOURIER WHERE ID=?";
    //快递员信息的录入
    public static final String C_INSERT = "INSERT INTO ECOURIER (CNAME,CPHONENUMBER,CIDCARDNUM,CPASSWORD,CSTATUS,CINTIME,CSENDNUMBER,CLASTLOGINTIME) VALUES(?,?,?,?,0,NOW(),500,NOW())";
    //快递员信息的修改
    public static final String C_UPDATE = "UPDATE ECOURIER SET CNAME=?,CPHONENUMBER=?,CIDCARDNUM=?,CPASSWORD=?,CLASTLOGINTIME=? WHERE ID=?";
    //快递员信息的删除
    public static final String C_DELETE = "DELETE FROM ECOURIER WHERE ID=?";

    /**
     * @return {size:总数,day:新增}
     * @Author LC
     * @Description //TODO 查询快递员表中所有记录包括总数和新增（新增就是注册时间与当前时间相同）
     * @Date 14:56 2021/9/3
     * @Param []
     **/
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_CONSOLE);
            //3.执行SQL语句
            result = state.executeQuery();
            //4.获取执行结果
            if (result.next()){
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                data.put("data_size",data_size);
                data.put("data_day",data_day);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param limit
     * @param offset
     * @param pageNumber
     * @return 快递员信息集合
     * @Author LC
     * @Description //TODO 查询所有快递员信息
     * @Date 15:02 2021/9/3
     * @Param [limit 是否分页的标记，true表示分页。false表示查询所有快递, offset SQL语句的起始索引, pageNumber 每一页查询的数量]
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            if (limit){
                state = conn.prepareStatement(C_FIND_LIMIT);
                //3.填充参数（可选）
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(C_FIND_ALL);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String cname = result.getString("cname");
                String cPhoneNumber = result.getString("cphonenumber");
                String cIdCardNum = result.getString("cidcardnum");
                String cPassword = result.getString("cpassword");
                int cStatus = result.getInt("cstatus");
                String cSendNumber = result.getString("csendnumber");
                Date cInTime = result.getTime("cintime");
                Date cLastLoginTime = result.getTime("clastlogintime");
                Courier c = new Courier(id,cname,cPhoneNumber,cIdCardNum,cPassword,cStatus,cInTime,cSendNumber,cLastLoginTime);
                data.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param phoneNumber
     * @return 查询的快递员信息，手机号不存在时返回null
     * @Author LC
     * @Description //TODO 根据快递员手机号查找对应快递员
     * @Date 15:04 2021/9/3
     * @Param [phoneNumber 快递员手机号]
     */
    @Override
    public Courier findByPhoneNumber(String phoneNumber) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_FIND_BY_PHONENUMBER);
            //3.填充参数（可选）
            state.setString(1,phoneNumber);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int id = result.getInt("id");
                String cname = result.getString("cname");
                String cPhoneNumber = result.getString("cphonenumber");
                String cIdCardNum = result.getString("cidcardnum");
                String cPassword = result.getString("cpassword");
                int cStatus = result.getInt("cstatus");
                Date cInTime = result.getTime("cintime");
                String cSendNumber = result.getString("csendnumber");
                Date cLastLoginTime = result.getTime("clastlogintime");
                Courier c = new Courier(id,cname,cPhoneNumber,cIdCardNum,cPassword,cStatus,cInTime,cSendNumber,cLastLoginTime);
                return c;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param id
     * @return 查询的快递员信息，id不存在时返回null
     * @Author LC
     * @Description //TODO 根据快递员主键id查找对应快递员
     * @Date 16:29 2021/9/3
     * @Param [id 快递员主键id]
     */
    @Override
    public Courier findById(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_FIND_BY_ID);
            //3.填充参数（可选）
            state.setInt(1,id);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                String cname = result.getString("cname");
                String cPhoneNumber = result.getString("cphonenumber");
                String cIdCardNum = result.getString("cidcardnum");
                String cPassword = result.getString("cpassword");
                int cStatus = result.getInt("cstatus");
                Date cInTime = result.getTime("cintime");
                String cSendNumber = result.getString("csendnumber");
                Date cLastLoginTime = result.getTime("clastlogintime");
                Courier c = new Courier(id,cname,cPhoneNumber,cIdCardNum,cPassword,cStatus,cInTime,cSendNumber,cLastLoginTime);
                return c;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param c
     * @return 录入的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 快递员信息的录入
     * @Date 15:06 2021/9/3
     * @Param [c 快递员信息对象]
     */
    @Override
    public boolean insert(Courier c) throws DuplicateCodeException {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_INSERT);
            //3.填充参数
            state.setString(1,c.getCname());
            state.setString(2,c.getcPhoneNumber());
            state.setString(3,c.getcIdCardNum());
            state.setString(4,c.getcPassword());
            //4.执行SQL语句，并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            if (e.getMessage().endsWith("for key 'phonenumber'")){
                //因为快递员电话号码重复而出现异常
                DuplicateCodeException e1 = new DuplicateCodeException(e.getMessage());
                throw e1;
            }else {
                e.printStackTrace();
            }
        } finally {
            //5.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @param newc
     * @return 更新的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 快递员信息的更新
     * @Date 15:07 2021/9/3
     * @Param [id 要更新的快递员id, newc 新的快递员信息对象]
     */
    @Override
    public boolean update(int id, Courier newc) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_UPDATE);
            state.setString(1,newc.getCname());
            state.setString(2,newc.getcPhoneNumber());
            state.setString(3,newc.getcIdCardNum());
            state.setString(4,newc.getcPassword());
            //将java.util.date转换为java.sql.date
            java.sql.Date date = new java.sql.Date(newc.getcLastLoginTime().getTime());
            state.setDate(5,date);
            state.setInt(6,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //3.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @return 删除的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 根据快递员的id删除相应数据
     * @Date 15:08 2021/9/3
     * @Param [id 快递员的id]
     */
    @Override
    public boolean delete(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(C_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //3.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
